﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace APSIM
{
    class ExcelOperate
    {
        //打开外部文件，对其进行修改
        public void xlsFileOperate(string strPath, string strlatitude, string strTAV, string strAMP, string strPathSaveAs)
        {
            //缺省值
            object oMissing = System.Reflection.Missing.Value;
            //Application对象 一个Excel工作薄
            Excel.Application oExcel = new Excel.Application();
            //Workbook对象 一个Excel工作表 
            Excel.Workbook oBook = oExcel.Workbooks.Open(strPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets["Sheet1"];

            Excel.Range orange = (Excel.Range) oSheet.get_Range("A1","H18300");
            orange.ColumnWidth = 10;
            Excel.Range orang = (Excel.Range)oSheet.get_Range("A1", "H18300");
            orang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                        
            Excel.Range oRange = (Excel.Range)oSheet.Rows[1, Type.Missing];
            oRange.EntireRow.Delete(Excel.XlDirection.xlDown);
            //插入9行
            Excel.Range oRange2 = (Excel.Range)oSheet.Rows[1, Type.Missing];
            int intRows = 9;
            for (int i = 0; i < intRows; i++)
            {
                oRange2.Rows.Insert(Excel.XlDirection.xlDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            }
            oExcel.Cells[1, 1] = "!Title = Goondiwindi 1940-1989";
            oExcel.Cells[2, 1] = "";
            oExcel.Cells[3, 1] = "[weather.met.weather]";
            oExcel.Cells[4, 1] = "latitude=";
            oExcel.Cells[4, 2] = strlatitude.Trim();
            oExcel.Cells[5, 1] = "";

            oExcel.Cells[6, 1] = "tav =";
            oExcel.Cells[6, 2] = strTAV.Trim();
            oExcel.Cells[6, 3] = "(oC)";
            oExcel.Cells[6, 4] = "! annual average ambient temperature";

            oExcel.Cells[7, 1] = "amp =";
            oExcel.Cells[7, 2] = strAMP.Trim();
            oExcel.Cells[7, 3] = "(oC)";
            oExcel.Cells[7, 4] = "! annual amplitude in mean monthly temperature";

            oExcel.Cells[8, 1] = "   site";
            oExcel.Cells[8, 2] = "   year";
            oExcel.Cells[8, 3] = "   day";
            oExcel.Cells[8, 4] = "   radn";
            oExcel.Cells[8, 5] = "   maxt";
            oExcel.Cells[8, 6] = "   mint";
            oExcel.Cells[8, 7] = "   rain";
            oExcel.Cells[8, 8] = "   evap";


            oExcel.Cells[9, 1] = "    ()";
            oExcel.Cells[9, 2] = "    ()";
            oExcel.Cells[9, 3] = "    ()";
            oExcel.Cells[9, 4] = "(MJ/m^2)";
            oExcel.Cells[9, 5] = "    (oC)";
            oExcel.Cells[9, 6] = "    (oC)";
            oExcel.Cells[9, 7] = "    (mm)";
            oExcel.Cells[9, 8] = "    (mm)";

            oSheet.SaveAs(strPathSaveAs, Excel.XlFileFormat.xlTextPrinter, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            oExcel.Quit();
        }



    }
}

